package com.ssy.lingxi.report.dao.purchase;

import com.ssy.lingxi.report.entity.report.PurchaseDayReportDO;
import com.ssy.lingxi.report.model.bo.tally.statistics.PlatformPurchaseStatisticsTallyVO;
import com.ssy.lingxi.report.model.bo.tally.statistics.PurchaseConfirmQuotedPriceStatisticsTallyVO;
import com.ssy.lingxi.report.model.bo.tally.statistics.PurchaseInquiryStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 *  采购询价DAO
 * @author ds
 * @date 2021/5/11
 * @version 2.0.0
 */
@Service
public class PurchaseInquiryDAO {

    @Resource
    @Qualifier("purchaseJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 采购询价-统计数量
     * @return
     */
    public List<PurchaseInquiryStatisticsTallyVO> getStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" create_member_id memberId,create_member_role_id roleId,")
                .append(" sum(case when (external_state = 5 or interior_state in (1,8,9) )then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when (external_state = 1 and interior_state=2 )then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when (interior_state=3 )then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when (interior_state = 4 )then 1 else 0 end) toBeCommitCount")
                .append(" FROM pu_purchase_inquiry ")
                .append(" group by create_member_id,create_member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseInquiryStatisticsTallyVO.class));
    }

    /**
     * 确认报价-统计数量
     * @return
     */
    public List<PurchaseConfirmQuotedPriceStatisticsTallyVO> getConfirmQuotedPriceStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" create_member_id memberId,create_member_role_id roleId,")
                .append(" sum(case when (confirm_interior_state in (21,25,26) and external_state=4 )then 1 else 0 end) toBeContrastPriceCount,")
                .append(" sum(case when (confirm_interior_state =22 and external_state=4 )then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when (confirm_interior_state =23 and external_state=4 )then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when (confirm_interior_state =24 and external_state=4 )then 1 else 0 end) toBeConfirmCount")
                .append(" FROM pu_purchase_inquiry ")
                .append(" group by create_member_id,create_member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseConfirmQuotedPriceStatisticsTallyVO.class));
    }

    /**
     * 平台采购统计[待审核采购询价]
     * @return
     */
    public PlatformPurchaseStatisticsTallyVO getPlatformStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" count(*) toBeVerifyInquiryCount")
                .append(" FROM pu_purchase_inquiry where external_state=2 ;");
        return jdbcTemplate.queryForObject(sql.toString(), new BeanPropertyRowMapper<>(PlatformPurchaseStatisticsTallyVO.class));
    }

    /**
     * 采购统计[平台审核通过的]
     * @return
     */
    public List<PurchaseDayReportDO> getPlatformExaminedStatisticsCount(Long startTime, Long endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" inq.create_member_id memberId,inq.create_member_role_id roleId,count(*) inquiryNum")
                .append(" FROM pu_purchase_inquiry inq")
                .append(" inner join pu_external_purchase_inquiry_log log on inq.id=log.purchase_inquiry_id ")
                .append(" where log.state=3 ")
                .append(" and log.create_time>=" + startTime)
                .append(" and log.create_time<=" + endTime)
                .append(" group by inq.create_member_id,inq.create_member_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseDayReportDO.class));
    }
}
